select @docTypeFolderType:=FolderTypeId from FolderType where Name='DocType';
select @documentFolderType:=FolderTypeId from FolderType where Name='Document';
select @rootDocTypeFolder:=FolderId from Folder where FolderType=@docTypeFolderType and IsNull(ParentFolder);
select @rootDocumentFolder:=FolderId from Folder where FolderType=@documentFolderType and IsNull(ParentFolder);
select @testDocTypeFolder1:=FolderId from Folder where Name='Test DocType folder1';
select @adminRole:=DcRoleId from DcRole where RoleName='Administrators';
select @userRole:=DcRoleId from DcRole where RoleName='Users';
select @docCircAdmin:=DcUserId from DcUser where Login='DocCircAdmin';
select @docCircUser:=DcUserId from DcUser where Login='DocCircUser';
select @docCircUser1:=DcUserId from DcUser where Login='DocCircUser1';
select @decisionRIT:=RouteItemTypeId from RouteItemType where Name='Decision';

-- add two new roles
insert into DcRole (DcRoleId, RoleName) Values
	(default, 'saTestRole1'),
	(default, 'saTestRole2');
set @saTestRole1 = last_insert_id();
set @saTestRole2 = last_insert_id() + 1;

-- add new roles to DocCircUser1
insert into RoleToUser (RoleToUserId, DcRole, DcUser, Position)
	values (default, @saTestRole1, @docCircUser1, 1),
	(default, @saTestRole2, @docCircUser1, 1);

-- insert two new doc type folders
insert into Folder (FolderId, FolderType, ParentFolder, Name) Values 
	(default, @docTypeFolderType, @rootDocTypeFolder, 'saTestFolder1'),
	(default, @docTypeFolderType, @rootDocTypeFolder, 'saTestFolder2');
set @saTestFolder1 = last_insert_id();
set @saTestFolder2 = last_insert_id() + 1;

insert into FolderPermission (Folder, DcRole, C, R, U, D, B, RO, UO, DO)
	select @saTestFolder1, DcRole, C, R, U, D, B, RO, UO, DO from NewFolderPermission
	where FolderType=@docTypeFolderType;
insert into FolderPermission (Folder, DcRole, C, R, U, D, B, RO, UO, DO)
	select @saTestFolder2, DcRole, C, R, U, D, B, RO, UO, DO from NewFolderPermission
	where FolderType=@docTypeFolderType;

-- add the two new role adjustments for each folder
insert into FolderPermission (Folder, DcRole, C, R, U, D, B, RO, UO, DO) values
	--								C		R	  U		 D		B	 RO    UO    DO
	(@saTestFolder1, @saTestRole1, true,  true, false, false, true, true, false, false),
	(@saTestFolder1, @saTestRole2, false, true, false, false, true, true, false, true),
	
	(@saTestFolder2, @saTestRole1, false, true, false, false, true, true, true, false),
	(@saTestFolder2, @saTestRole2, false, true, true,  true,  true, true, true, true);

-- Add DocType: "Test type first" into root DocType folder by DocCircAdmin
insert into DocType (DocTypeId, Folder, Name, Description, Prefix, NextNumber, IsNew, DcUser) values 
(default, @rootDocTypeFolder, 'Test type first', 'first type', 'TTF', 1, false, @docCircAdmin); 
set @docType1:=last_insert_id(); 
--
	insert into RoutePoint (DocType, OrderNum, Expression) Values (@docType1, 1, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @DocType1, @docCircUser1, @decisionRIT, 0);
--
insert into DocTypePermission (DocType, DcRole, C, R, U, D, RO, UO, DO) 
	select @docType1, DcRole, C, R, U, D, RO, UO, DO from NewDocTypePermission;

-- prohibit any access to DocType1 for User role
update DocTypePermission set C=false, R=false, U=false, D=false, RO=false, UO=false, DO=false 
	where DocType=@docType1 and DcRole=@userRole;
--	

-- Add DocType: "Test type second" into root DocType Folder by DocCircAdmin
insert into DocType (DocTypeId, Folder, Name, Description, Prefix, NextNumber, IsNew, DcUser) values 
(default, @rootDocTypeFolder, 'Test type second', 'second type', 'TTS', 1, false, @docCircAdmin); 
set @docType2:=last_insert_id(); 
--
	insert into RoutePoint (DocType, OrderNum, Expression) Values (@docType2, 1, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @DocType2, @docCircUser1, @decisionRIT, 0);
--
insert into DocTypePermission (DocType, DcRole, C, R, U, D, RO, UO, DO) 
	select @docType2, DcRole, C, R, U, D, RO, UO, DO from NewDocTypePermission;

-- prohibit any access to DocType1 for User role
update DocTypePermission set C=false, R=false, U=false, D=false, RO=false, UO=false, DO=false 
	where DocType=@docType2 and DcRole=@userRole;
--	


-- add the two new role adjustments for the doc types	
insert into DocTypePermission (DocType, DcRole, C, R, U, D, RO, UO, DO) values
	--						   C	   R	 U		D	  RO    UO    DO
	(@docType1, @saTestRole1, true,  true, false, false, true, false, false),
	(@docType1, @saTestRole2, false, true, false, false, true, false, true),
	
	(@docType2, @saTestRole1, true,  true, false, false, false,false, false),
	(@docType2, @saTestRole2, true,  false,false, false, true, true, false);
	